﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WF_Business;
namespace Business.Admin
{
    public class JieDaiInfo
    {

        /// <summary>
        /// 按照部门获取接待信息统计表
        /// </summary>
        /// <param name="strDateStart">开始时间</param>
        /// <param name="strDateEnd">终止时间</param>
        /// <returns></returns>
        public static DataTable GetJieDaiCollectInfo(string strDateStart, string strDateEnd)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(strDateStart))
            {
                strWhere = string.Format(" and b.finish_time >= to_date('{0}', 'yyyy-mm-dd')", strDateStart);
            }
            if (!string.IsNullOrEmpty(strDateEnd))
            {
                strWhere += string.Format(" and b.finish_time <= to_date('{0}', 'yyyy-mm-dd')", strDateEnd);
            }

            string strSql = string.Format(@"select t.费用归口部门 bm, sum(t.实际总消费) zxf，count(1) xfcs 
              from UT_接待安排通知单 t,st_instance b
             where t.iid=b.iid {0}
             group by t.费用归口部门", strWhere);
            DataTable dtSource = new DataTable();

            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }

        /// <summary>
        /// 按照部门获取接待信息统计表
        /// </summary>
        /// <param name="strDateStart">开始时间</param>
        /// <param name="strDateEnd">终止时间</param>
        /// <param name="strBm">部门信息</param>
        /// <returns></returns>
        public static DataTable GetJieDaiDetailInfo(string strDateStart, string strDateEnd, string strBm)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(strDateStart))
            {
                strWhere = string.Format(" and b.finish_time >= to_date('{0}', 'yyyy-mm-dd')", strDateStart);
            }
            if (!string.IsNullOrEmpty(strDateEnd))
            {
                strWhere += string.Format(" and b.finish_time <= to_date('{0}', 'yyyy-mm-dd')", strDateEnd);
            }
            if (!string.IsNullOrEmpty(strBm))
            {
                strWhere += string.Format(" and t.费用归口部门 like '%{0}%'", strBm);
            }

            string strSql = string.Format(@"select t.费用归口部门 bm, t.实际总消费 zxf,t.iid  
              from UT_接待安排通知单 t,st_instance b
             where t.iid=b.iid {0}", strWhere);
            DataTable dtSource = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }

    }
}
